package com.sptci.rwt;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Collection;

import com.sptci.util.CloseJDBCResources;

/**
 * An analyser for analysing view type objects in the database. View 
 * information is retrieved from the <code>Information Schema</code>.
 *
 * <p>&copy; Copyright 2007 Sans Pareil Technologies, Inc.</p>
 * @author Rakesh Vidyadharan 2007-09-26
 * @version $Id: ViewAnalyser.java 2 2007-10-19 21:06:36Z rakesh.vidyadharan $
 */
public class ViewAnalyser extends Analyser
{
  /**
   * The <code>types</code> parameters to specify while invoking the
   * {@link java.sql.DatabaseMetaData#getTables} method.
   */
  private static final String[] TYPE = new String[] { "VIEW" };

  /**
   * Create a new instance of the class using the specified connection
   * manager.
   *
   * @param manager The manager for obtaining database connections.
   */
  public ViewAnalyser( final ConnectionManager manager )
  {
    super( manager );
  }

  /**
   * Returns a collection of {@link ViewMetaData} objects that contain the
   * basic information pertaining to the views in the schema.
   *
   * @see Analyser#analyse
   * @see #getMetaData( ViewMetaData, PreparedStatement )
   * @see #getMetaData( ViewMetaData, PreparedStatement, SchemaMetaData )
   * @param parameters Must contain one parameter which is a {@link
   *   SchemaMetaData} that represents the <code>schema</code> to restrict
   *   the analysis to.  The name of the <code>schema</code> may be
   *   <code>null</code> or an empty string.
   */
  @Override
  public Collection<ViewMetaData> analyse( final MetaData... parameters )
    throws SQLException
  {
    Collection<ViewMetaData> collection = new ArrayList<ViewMetaData>();
    Connection connection = null;
    ResultSet resultSet = null;
    PreparedStatement schemaStatement = null;
    PreparedStatement noSchemaStatement = null;

    String noSchemaQuery = "select view_definition, check_option, is_updatable, is_insertable_into from information_schema.views where table_name = ?";
    String schemaQuery = "select view_definition, check_option, is_updatable, is_insertable_into from information_schema.views where table_name = ? and table_schema = ?";

    try
    {
      String schema = ( parameters[0] == null ) ? null : parameters[0].getName();
      connection = manager.open();
      DatabaseMetaData dmd = connection.getMetaData();
      resultSet = dmd.getTables( null, schema, "%", TYPE );

      noSchemaStatement = connection.prepareStatement( noSchemaQuery );
      schemaStatement = connection.prepareStatement( schemaQuery );


      while ( resultSet.next() )
      {
        ViewMetaData vmd = new ViewMetaData();

        vmd.setName( resultSet.getString( "table_name" ) );
        vmd.setComment( resultSet.getString( "remarks" ) );

        if ( parameters[0] == null )
        {
          getMetaData( vmd, noSchemaStatement );
        }
        else
        {
          getMetaData( vmd, schemaStatement, (SchemaMetaData) parameters[0] );
        }

        collection.add( vmd );
      }

      if ( parameters[0] != null )
      {
        ( (SchemaMetaData) parameters[0] ).setViews( collection );
      }
    }
    finally
    {
      CloseJDBCResources.close( resultSet );
      CloseJDBCResources.close( noSchemaStatement );
      CloseJDBCResources.close( schemaStatement );
      CloseJDBCResources.close( connection );
    }

    if ( collection.isEmpty() )
    {
      getViews( (SchemaMetaData) parameters[0], collection );
    }

    return collection;
  }

  /**
   * Fetch additional metadata for the specified view from the
   * <code>Information Schema</code>.
   *
   * @param vmd The metadata object for the view that will be updated.
   * @param statement The statement to execute.
   * @throws SQLException If errors are encountered while fetching the views.
   */
  protected void getMetaData( final ViewMetaData vmd,
      final PreparedStatement statement ) throws SQLException
  {
    ResultSet resultSet = null;

    try
    {
      statement.clearParameters();
      statement.setString( 1, vmd.getName() );
      resultSet = statement.executeQuery();

      if ( resultSet.next() )
      {
        vmd.setDefinition( resultSet.getString( "view_definition" ) );
        vmd.setCheckOption( resultSet.getString( "check_option" ) );
        vmd.setUpdatable( resultSet.getBoolean( "is_updatable" ) );
        vmd.setInsertable( resultSet.getBoolean( "is_insertable_into" ) );
      }
    }
    finally
    {
      CloseJDBCResources.close( resultSet );
    }
  }

  /**
   * Fetch additional metadata for the specified view from the
   * <code>Information Schema</code>.
   *
   * @param vmd The metadata object for the view that will be updated.
   * @param statement The statement to execute.
   * @param smd The metadata object for the schema to restrict searches to.
   * @throws SQLException If errors are encountered while fetching the views.
   */
  protected void getMetaData( final ViewMetaData vmd,
      final PreparedStatement statement, final SchemaMetaData smd )
    throws SQLException
  {
    ResultSet resultSet = null;

    try
    {
      statement.clearParameters();
      statement.setString( 1, vmd.getName() );
      statement.setString( 2, smd.getName() );
      resultSet = statement.executeQuery();

      if ( resultSet.next() )
      {
        vmd.setDefinition( resultSet.getString( "view_definition" ) );
        vmd.setCheckOption( resultSet.getString( "check_option" ) );
        vmd.setUpdatable( resultSet.getBoolean( "is_updatable" ) );
        vmd.setInsertable( resultSet.getBoolean( "is_insertable_into" ) );
      }
    }
    finally
    {
      CloseJDBCResources.close( resultSet );
    }
  }

  /**
   * Fetch the views directly from <code>information_schema</code> if no
   * information is available through {@link java.sql.DatabaseMetaData}.
   *
   * @param schema The metadata object that represents the schema for
   *   which the views are to be retrieved.
   * @param collection The collection to which the {@link ViewMetaData}
   *   objects are to be added.
   * @throws SQLException If errors are encountered while fetching the views.
   */
  protected void getViews( final SchemaMetaData schema,
      final Collection<ViewMetaData> collection ) throws SQLException
  {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      final String query = "select * from information_schema.views" +
        ( ( schema == null ) ? "" :
          " where table_schema = '" + schema.getName() + "'" );
      connection = manager.open();
      statement = connection.createStatement();
      resultSet = statement.executeQuery( query );

      while ( resultSet.next() )
      {
        ViewMetaData vmd = new ViewMetaData();

        vmd.setName( resultSet.getString( "table_name" ) );
        vmd.setDefinition( resultSet.getString( "view_definition" ) );
        vmd.setCheckOption( resultSet.getString( "check_option" ) );
        vmd.setUpdatable( resultSet.getBoolean( "is_updatable" ) );
        vmd.setInsertable( resultSet.getBoolean( "is_insertable_into" ) );

        collection.add( vmd );
      }

      if ( schema != null ) schema.setViews( collection );
     
    }
    finally
    {
      CloseJDBCResources.close( resultSet );
      CloseJDBCResources.close( statement );
      CloseJDBCResources.close( connection );
    }
  }
}
